﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Reflection;
using System.Runtime.InteropServices;
using Excel = Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using r2mp_DataStructures;
using System.Runtime.Serialization.Formatters.Binary;

namespace r2mp_Excel
{
    class Extractor
    {
        private Excel.Application app = new Excel.Application();
        private Excel.Worksheet mta = null;
        private Excel.Worksheet psp = null;

        //These are lists which contain all diffrent names for the colmuns
        List<string> mtaNames = new List<string>();
        List<string> mtaDates = new List<string>();
        List<string> mtaControls = new List<string>();
        List<string> pspNames = new List<string>();
        List<string> pspNrs = new List<string>();
        List<string> pspDurations = new List<string>();
        List<string> pspCompletes = new List<string>();
        List<string> pspRessources = new List<string>();

        //These dicionarys are used to save the initial of the column where a match was found.
        Dictionary<string, string> mtaMap = new System.Collections.Generic.Dictionary<string, string>();
        Dictionary<string, string> pspMap = new System.Collections.Generic.Dictionary<string, string>();

        private int periode = 0;
        private int mtaRow = 1;
        private int pspRow = 1;
        private Task start = new Task();


        public void OpenFile(string path)
        {
            //Loads the workbook into the program.
            Excel.Workbook theWorkbook = app.Workbooks.Open(
               path, 0, true, 5,
                "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
                0, true);

            loadSettings();

            //Searches all worksheets in the workbook for worksheets which are useable for MTA or PSP
            foreach (Excel.Worksheet now in theWorkbook.Worksheets)
            {
                int row = 1;
                string inital = "A";
                row = 1;
                //Checks from A1 to AAA10 and for a name MTA column
                while (row < 11 && mta == null)
                {
                    while (inital != "AAA" && mta == null)
                    {
                        foreach (string name in mtaNames)
                        {
                            if ((now.get_Range(inital + row.ToString()).Value + "").Equals(name))
                            {
                                mtaMap["Name"] = inital;
                                if (IsMTA(now))
                                {
                                    mta = now;
                                    mtaRow = row;
                                    break;
                                }
                                else
                                {
                                    mtaMap.Remove("Name");
                                }
                            }
                        }
                        inital = GetNextInitial(inital);
                    }
                    row++;
                }
                inital = "A";
                row = 1;
                //Checks from A1 to AAA10 and for a PSP name column
                while (row < 11 && psp == null)
                {
                    while (inital != "AAA" && psp == null)
                    {
                        foreach (string name in pspNames)
                        {
                            if ((now.get_Range(inital + row.ToString()).Value + "").Equals(name))
                            {
                                pspMap["Name"] = inital;
                                if (IsPSP(now))
                                {
                                    psp = now;
                                    pspRow = row;
                                    break;
                                }
                                else
                                {
                                    pspMap.Remove("Name");
                                }
                            }
                        }
                        inital = GetNextInitial(inital);
                    }
                    row++;
                }
            }

        }
        
        /// <summary>
        /// Checks if the given worksheet is a suitable PSP
        /// </summary>
        /// <param name="now">The Worksheet which you want to check if it is a PSP</param>
        /// <returns>Returns a boolean if it is suiteable or not</returns>
        private bool IsPSP(Excel.Worksheet now)
        {
            bool re = true;
            try
            {
                string inital = "A";
                //Checks from A1 to AAA10 and for a PSP number column
                while (inital != "AAA" && !pspMap.ContainsKey("Nr"))
                {
                    foreach (string nr in pspNrs)
                    {
                        if ((now.get_Range(inital + pspRow.ToString()).Value + "").Equals(nr))
                        {
                            pspMap["Nr"] = inital;
                            break;
                        }
                    }
                    inital = GetNextInitial(inital);
                }

                
                inital = "A";
                //Checks from A1 to AAA10 and for a PSP completion column
                while (inital != "AAA" && !pspMap.ContainsKey("Fertigstellung"))
                {
                    foreach (string complete in pspCompletes)
                    {
                        if ((now.get_Range(inital + pspRow.ToString()).Value + "").Contains(complete))
                        {
                            pspMap["Fertigstellung"] = inital;
                            break;
                        }
                    }
                    inital = GetNextInitial(inital);
                }

                
                inital = "A";
                //Checks from A1 to AAA10 and for a PSP ressource column
                while (inital != "AAA" && !pspMap.ContainsKey("Ressourcen"))
                {
                    foreach (string ressource in pspRessources)
                    {
                        if ((now.get_Range(inital + pspRow.ToString()).Value + "").Contains(ressource))
                        {
                            pspMap["Ressourcen"] = inital;
                            break;
                        }
                    }
                    inital = GetNextInitial(inital);
                }
            }
            catch
            {
                re = false;
            }
            //Completion and number columns are mandatory, ressources are optional
            if (pspMap.ContainsKey("Fertigstellung") && pspMap.ContainsKey("Nr"))
            {
                re = true;
            }
            else
            {
                re = false;
                psp = null;
            }
            return re;
        }

        /// <summary>
        /// Checks if the given worksheet is a suitable MTA
        /// </summary>
        /// <param name="now">The Worksheet which you want to check if it is a MTA</param>
        /// <returns>Returns a boolean if it is suiteable or not</returns>
        private bool IsMTA(Excel.Worksheet now)
        {
            bool re = true;
            try
            {
                
                string inital = GetNextInitial(mtaMap["Name"]);
                //Checks from A1 to AAA10 and for a MTA date column
                while (inital != "AAA" && !mtaMap.ContainsKey("Date"))
                {
                    foreach (string date in mtaDates)
                    {
                        if ((now.get_Range(inital + mtaRow.ToString()).Value + "").Equals(date))
                        {
                            mtaMap["Date"] = inital;
                            break;
                        }
                    }
                    inital = GetNextInitial(inital);
                }

                inital = mtaMap["Date"];
                //Checks from the date cell to AAA10 and for a MTA controldate column
                while (inital != "AAA" && !mtaMap.ContainsKey("Control"))
                {
                    foreach (string control in mtaControls)
                    {
                        if ((now.get_Range(inital + mtaRow.ToString()).Value + "").Contains(control))
                        {
                            mtaMap["Control"] = inital;
                            break;
                        }
                    }
                    inital = GetNextInitial(inital);
                }
            }
            catch
            {
                re = false;
            }
            //Date and controldate columns are mandatory.
            if (mtaMap.ContainsKey("Control") && mtaMap.ContainsKey("Date"))
            {
                re = true;
            }
            else
            {
                re = false;
                mta = null;
            }
            return re;
        }

        /// <summary>
        /// Opens "pattern.rin" if it exists and fills the namelists with values.
        /// </summary>
        private void loadSettings()
        {
            try
            {
                Stream stream = File.Open("pattern.rin", FileMode.Open);
                BinaryFormatter bFormatter = new BinaryFormatter();
                foreach (string now in ((string[])bFormatter.Deserialize(stream)))
                {
                    string[] temp = now.Split(':');
                    if (temp[0].Equals("Name"))
                    {
                        mtaNames.Add(temp[1]);
                    }
                    else if (temp[0].Equals("Enddatum"))
                    {
                        mtaDates.Add(temp[1]);
                    }
                    else
                    {
                        mtaControls.Add(temp[1]);
                    }
                }
                foreach (string now in ((string[])bFormatter.Deserialize(stream)))
                {
                    string[] temp = now.Split(':');
                    if (temp[0].Equals("Name"))
                    {
                        pspNames.Add(temp[1]);
                    }
                    else if (temp[0].Equals("Nr"))
                    {
                        pspNrs.Add(temp[1]);
                    }
                    else if (temp[0].Equals("Dauer"))
                    {
                        pspDurations.Add(temp[1]);
                    }
                    else if (temp[0].Equals("Fertigstellung"))
                    {
                        pspCompletes.Add(temp[1]);
                    }
                    else
                    {
                        pspRessources.Add(temp[1]);
                    }
                }
                //settingsPSP = ((string[])bFormatter.Deserialize(stream));

                stream.Close();
            }
            catch (IOException io)
            {
                MessageBox.Show("Ein unbekannter Fehler beim Lesen oder Schreiben ist aufgetreten.\n" +
                    "Wenden Sie sich bitte an Ihren Administrator", "Schreibe-/Lese-Fehler", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
            catch (Exception ex)
            {

            }
        }

        /// <summary>
        /// Creates a MTA object and fills it with milestones from the worksheet.
        /// </summary>
        /// <param name="periode">Determines if the MTA is created with weeks or month in between the
        /// milestone controldates</param>
        /// <returns>Returns a MTA object filled with milestones from the worksheet</returns>
        public MTA CreateMTA(int periode)
        {
            MTA milestones = new MTA(); ;
            if (mta != null)
            {

                int rows = CountRows(mtaMap["Name"], mta);
                if (rows == 0)
                {
                    MessageBox.Show("Arbeitsblatt besitzt kein gültiges Format");
                }
                else
                {
                    try
                    {
                        milestones.Periode = periode;
                        this.periode = periode;
                        for (int i = 0; i < rows; i++)
                        {
                            milestones.Milestones.Add(CreateMilestone(mtaRow + i));
                        }
                    }
                    catch (FormatException e)
                    {
                        MessageBox.Show("Das Blatt enthält ungültige Daten");
                    }
                }
            }
            return milestones;
        }

        /// <summary>
        /// Creates a Milestone object filled with values from the worksheet
        /// </summary>
        /// <param name="i">The row position within the worksheet for the wanted milestone</param>
        /// <returns>Returns a Milestone object filled with values from the worksheet</returns>
        private Milestone CreateMilestone(int i)
        {
            List<DateTime> dates = new List<DateTime>();
            // i += 1 because i starts to count from the title column          
            i += 1;
            int offset = 1;

            Milestone re = new Milestone();
            DateTime second = DateTime.Now;
            re.Name = mta.get_Range(mtaMap["Name"].ToString() + i).Value + "";
            re.PlannedCompletionDate = DateTime.Parse(mta.get_Range(mtaMap["Date"].ToString() + i).Value + "");

            string inital = mtaMap["Control"];
            while (!(mta.get_Range(inital + "" + i).Value + "").Equals(""))
            {
                try
                {
                    if (!(mta.get_Range(inital + "" + i).Value + "").Equals("NV"))
                    {
                        second = DateTime.Parse(mta.get_Range(inital + "" + i).Value + "");
                        if (re.Timeshifts.Count == 0)
                        {
                            re.PlannedCompletionDate = DateTime.Parse(mta.get_Range(inital + "" + i).Value + "");
                            dates.Add(DateTime.Parse(mta.get_Range(inital + "" + i).Value + ""));
                            re.Timeshifts.Add(0);
                        }
                        else
                        {
                            //Checks if the current date and the one before it are the same.
                            if (dates[dates.Count - 1] != second)
                            {
                                re.Timeshifts.Add(GetOffset(dates[dates.Count - 1], second));
                            }
                            else
                            {
                                re.Timeshifts.Add(0);
                            }
                            dates.Add(second);
                        }
                    }
                    offset++;
                    inital = GetNextInitial(inital);
                }
                catch (FormatException e)
                {
                    throw e;
                }
            }
            //The methode adds a 0 for the start date of a milestone and this is unwanted in the painting process.
            re.Timeshifts.Remove(0);
            return re;
        }

        /// <summary>
        /// Calcutaes the offset of two dates.
        /// </summary>
        /// <param name="from">The date which you want to check.</param>
        /// <param name="to">The date which you want to test the first one with.</param>
        /// <returns>Returns an integer representing the offset from the dates.</returns>
        private int GetOffset(DateTime from, DateTime to)
        {
            bool found = false;
            int re = 0;
            if (periode == MTA.Month)
            {
                DateTime monthBegin = new DateTime(from.Year, from.Month, 1);
                DateTime monthEnd = new DateTime(to.Year, to.Month, 1);
                DateTime currentMonth = monthBegin;
                if (monthBegin == monthEnd)
                {
                    re = 0;
                }
                else if (monthBegin < monthEnd)
                {
                    while (currentMonth < monthEnd)
                    {
                        re++;
                        currentMonth = currentMonth.AddMonths(1);
                    }
                }
                else
                {
                    re = -1 * GetOffset(to, from);
                }
            }
            else
            {
                switch (from.DayOfWeek)
                {
                    case DayOfWeek.Tuesday:
                        from = from.AddDays(-1);
                        break;
                    case DayOfWeek.Wednesday:
                        from = from.AddDays(-2);
                        break;
                    case DayOfWeek.Thursday:
                        from = from.AddDays(-3);
                        break;
                    case DayOfWeek.Friday:
                        from = from.AddDays(-4);
                        break;
                    case DayOfWeek.Saturday:
                        from = from.AddDays(-5);
                        break;
                    case DayOfWeek.Sunday:
                        from = from.AddDays(-6);
                        break;
                }

                if (from.Date > to.Date)
                {
                    re--;
                    while (!found)
                    {
                        if (from.AddDays(-7) < to)
                        {
                            found = true;
                        }
                        else
                        {
                            from = from.AddDays(-7);
                            re--;
                        }
                    }
                }
                else if (from.Date < to.Date)
                {
                    while (!found)
                    {
                        if (from.AddDays(7) > to)
                        {
                            found = true;
                        }
                        else
                        {
                            re++;
                            from = from.AddDays(7);
                        }
                    }
                }
                else
                {
                    found = true;
                }

            }
            return re;

        }
        /// <summary>
        /// Creates a tree of Task objects.
        /// </summary>
        /// <returns>Returns a Tas object which is the root of the tasktree.</returns>
        public Task CreatePSP()
        {
            Task temp;
            int rows = CountRows(pspMap["Name"], psp);
            try
            {
                for (int i = 0; i < rows; i++)
                {
                    temp = CreateTask(pspRow + i);
                    AddTask(start, temp);
                }
            }
            catch (FormatException e)
            {
                MessageBox.Show("Falsches Format der Daten");
            }
            return start;
        }

        /// <summary>
        /// Adds a given Task object to the tasktree at the right position.
        /// </summary>
        /// <param name="start">The root object of the tasktree.</param>
        /// <param name="temp">The Task object which shall be added.</param>
        private void AddTask(Task start, Task temp)
        {
            String[] parts = temp.Nr.Split('.');
            Task temptemp = start;
            //Traverse the tasktree with help of the number of the given Task object.
            for (int i = 0; i < parts.Length - 1; i++)
            {
                temptemp = temptemp.Tasks[Int32.Parse(parts[i]) - 1];
            }
            temptemp.Tasks.Add(temp);
        }


        /// <summary>
        /// Creates a Task object filled with values from the worksheet.
        /// </summary>
        /// <param name="i">The row position within the worksheet for the wanted task.</param>
        /// <returns>Returns a Task object filled with values from the worksheet.</returns>
        private Task CreateTask(int i)
        {
            try
            {
                // i += 1 because i starts to count from the title column
                i += 1;
                Task now = new Task();
                now.Nr = psp.get_Range(pspMap["Nr"].ToString() + i).Value + "";
                now.Name = psp.get_Range(pspMap["Name"].ToString() + i).Value + "";
                now.Status = GetStatus(Double.Parse((psp.get_Range(pspMap["Fertigstellung"].ToString() + i).Value + "")));
                if (pspMap.ContainsKey("Ressourcen"))
                {
                    CreateReassource(now, (psp.get_Range(pspMap["Ressourcen"].ToString() + i).Value + ""));
                }
                return now;
            }
            catch (FormatException e)
            {
                throw e;
            }
        }

        /// <summary>
        /// Splits the given String into ressources and adds them to the given task.
        /// </summary>
        /// <param name="now">The task in which the ressources should be added.</param>
        /// <param name="dynamic">This dynamic is a String containing the ressources seperated by ;.</param>
        private void CreateReassource(Task now, string dynamic)
        {
            String res = dynamic + "";
            String[] temp = res.Split(';');
            now.Ressources = new List<string>();
            for (int i = 0; i < temp.Length; i++)
            {
                now.Ressources.Add(temp[i]);
            }
        }

        /// <summary>
        /// Decide which status is represented by the double variable.
        /// </summary>
        /// <param name="p">Percentage of completion.</param>
        /// <returns>Returns Pending(0), Started(1), Finished(2).</returns>
        private int GetStatus(double p)
        {
            if (p == 0)
            {
                return Task.Pending;
            }
            else if (p == 1)
            {
                return Task.Finished;
            }
            else
            {
                return Task.Started;
            }
        }

        /// <summary>
        /// Counts the rows which are filled with values on the worksheet.
        /// </summary>
        /// <param name="initial">Sets the row from where it will be counted.</param>
        /// <param name="active">The current worksheet.</param>
        /// <returns>Returns the count of filled rows.</returns>
        private int CountRows(string initial, Excel.Worksheet active)
        {
            int temp;
            int re = 1;
            if (mta != null)
            {
                temp = mtaRow;
            }
            else
            {
                temp = pspRow;
            }
            bool end = false;
            while (!end)
            {
                temp = temp + 1;
                if (!(active.get_Range(initial + temp).Value + "").Equals(""))
                {
                    re++;
                }
                else
                {
                    end = true;
                }
            }
            return re - 1;
        }

        
        /// <summary>
        /// Gets the next initial of the next column. Can be tricky in Excel because after Z is AA
        /// </summary>
        /// <param name="now">The initial which will be increased.</param>
        /// <returns>The new initial which is increased by one column.</returns>
        private string GetNextInitial(string now)
        {
            Char[] temp = now.ToCharArray();
            increase(ref temp, temp.Length - 1);
            return new string(temp);
        }

        /// <summary>
        /// Helpermethod for increasing the initial of the column.
        /// </summary>
        /// <param name="temp">The chararray of the inital string.</param>
        /// <param name="i">The position of the last entry in the array.</param>
        private void increase(ref char[] temp, int i)
        {
            if (i == 0)
            {
                if (temp[i] == 'Z')
                {
                    temp[i] = 'A';
                    temp = (new string(temp) + "A").ToCharArray();
                }
                else
                {
                    temp[i] = (char)((int)temp[i] + 1);
                }
            }
            else
            {
                if (temp[i] == 'Z')
                {
                    temp[i] = 'A';
                    increase(ref temp, i - 1);
                }
                else
                {
                    temp[i] = (char)((int)temp[i] + 1);
                }
            }
        }

    }

}
